package com.teamwork;

import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SubmitExamAnswer implements Serializable {
    public static void submitAnswer(int studentId, int examId, int questionId, String studentAnswer) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

          
        try {conn = DBUtil.getConnection();  // 获取数据库连接

            // 检查是否存在该学生和考试的 SCId
            int scId = getSCId(conn, studentId, examId);

            // 如果 SCId 不存在，插入新的记录到 sc 表中
            if (scId == 0) {
                scId = insertSCRecord(conn, studentId, examId);
            }

            // 将学生的答案插入到 answer 表中
            String sql = "INSERT INTO answer (SId, EId, CHId, SAn, SCId) VALUES (?, ?, ?, ?, ?)";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, studentId);
            stmt.setInt(2, examId);
            stmt.setInt(3, questionId);
            stmt.setString(4, studentAnswer);
            stmt.setInt(5, scId);
            stmt.executeUpdate();

            System.out.println("答案成功提交!");  // 提交成功消息
        } catch (SQLException e) {
            e.printStackTrace();  // 打印异常信息
        } finally {
            try {
                // 关闭资源
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();  // 关闭资源时出现异常则打印异常信息
            }
        }
    }

    // 从 sc 表中检索 SCId 的方法
    private static int getSCId(Connection conn, int studentId, int examId) throws SQLException {
        int scId = 0;
        String sql = "SELECT SCId FROM sc WHERE SId = ? AND EId = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, studentId);
            stmt.setInt(2, examId);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                scId = rs.getInt("SCId");  // 从结果集中获取 SCId
            }
        }
        return scId;  // 返回获取到的 SCId
    }
    // 序列化方法
    public static void serializeObject(SubmitExamAnswer obj, String fileName) {
        try (FileOutputStream fos = new FileOutputStream(fileName);
             ObjectOutputStream oos = new ObjectOutputStream(fos)) {
            oos.writeObject(obj);
            System.out.println("对象已序列化到文件： " + fileName);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // 反序列化方法
    public static SubmitExamAnswer deserializeObject(String fileName) {
        SubmitExamAnswer obj = null;
        try (FileInputStream fis = new FileInputStream(fileName);
             ObjectInputStream ois = new ObjectInputStream(fis)) {
            obj = (SubmitExamAnswer) ois.readObject();
            System.out.println("对象已从文件反序列化： " + fileName);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
        return obj;
    }

    // 插入新记录到 sc 表中并返回生成的 SCId 的方法
    private static int insertSCRecord(Connection conn, int studentId, int examId) throws SQLException {
        int scId = 0;
        String sql = "INSERT INTO sc (SId, EId, SScore) VALUES (?, ?, 0)";
        try (PreparedStatement stmt = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS)) {
            stmt.setInt(1, studentId);
            stmt.setInt(2, examId);
            stmt.executeUpdate();
            ResultSet generatedKeys = stmt.getGeneratedKeys();
            if (generatedKeys.next()) {
                scId = generatedKeys.getInt(1);  // 获取生成的 SCId
            }
        }
        return scId;  // 返回生成的 SCId
    }

    public static void main(String[] args) {
        // 示例用法
        submitAnswer(1, 1, 2, "B");
	        SubmitExamAnswer answer = new SubmitExamAnswer();
        answer.submitAnswer(1, 1, 2, "B");

        // 序列化对象到文件
        String fileName = "answer.ser";
        serializeObject(answer, fileName);

        // 从文件中反序列化对象
        SubmitExamAnswer deserializedAnswer = deserializeObject(fileName);
    }
}
